package org.zjx.db;

import org.zjx.model.game.Association;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class AssociationDB {
    private static boolean isTableCreated = false;
    private final SQLiteHelper dbHelper;

    public AssociationDB() {
        dbHelper = SQLiteHelper.getSingleton();
        if (!isTableCreated) {
            createTable();
            isTableCreated = true;
        }
    }

    public void createTable() {
        String createTable = "CREATE TABLE IF NOT EXISTS association (\n"
                + "    id INTEGER PRIMARY KEY AUTOINCREMENT,\n"
                + "    serverId INTEGER NOT NULL,\n"
                + "    shopId INTEGER NOT NULL,\n"
                + "    shopType INTEGER NOT NULL,\n"
                + "    page INTEGER NOT NULL,\n"
                + "    itemName TEXT NOT NULL,\n"
                + "    state BOOLEAN NOT NULL\n"
                + ");";
        dbHelper.createTable(createTable);
    }

    public void insert(Association association) {
        String sql = "INSERT INTO association(serverId, shopId, shopType, page, itemName, state) VALUES(?, ?, ?, ?, ?, ?)";
        Object[] params = {
                association.getServerId(),
                association.getShopId(),
                association.getShopType(),
                association.getPage(),
                association.getItemName(),
                association.isState() ? 1 : 0
        };
        dbHelper.executeUpdate(sql, params);
    }

    public void update(Association association) {
        String sql = "UPDATE association SET serverId = ?, shopId = ?, shopType = ?, page = ?, itemName = ?, state = ? WHERE id = ?";
        Object[] params = {
                association.getServerId(),
                association.getShopId(),
                association.getShopType(),
                association.getPage(),
                association.getItemName(),
                association.isState() ? 1 : 0,
                association.getId()
        };
        dbHelper.executeUpdate(sql, params);
    }

    public void deleteById(int id) {
        String sql = "DELETE FROM association WHERE id = ?";
        Object[] params = {id};
        dbHelper.executeUpdate(sql, params);
    }

    public Association findById(int id) {
        String sql = "SELECT * FROM association WHERE id = ?";
        Object[] params = {id};
        List<Association> associations = dbHelper.executeQuery(sql, params, Association.class);
        return associations.isEmpty() ? null : associations.get(0);
    }

    public List<Association> findByServerId(int serverId) {
        String sql = "SELECT * FROM association WHERE serverId = ?";
        Object[] params = {serverId};
        return dbHelper.executeQuery(sql, params, Association.class);
    }

    public List<Association> findByShopId(int shopId) {
        String sql = "SELECT * FROM association WHERE shopId = ?";
        Object[] params = {shopId};
        return dbHelper.executeQuery(sql, params, Association.class);
    }

    public List<Association> findByShopType(int shopType) {
        String sql = "SELECT * FROM association WHERE shopType = ?";
        Object[] params = {shopType};
        return dbHelper.executeQuery(sql, params, Association.class);
    }

    public List<Association> findByItemName(String itemName) {
        String sql = "SELECT * FROM association WHERE itemName = ?";
        Object[] params = {itemName};
        return dbHelper.executeQuery(sql, params, Association.class);
    }

    public List<Association> findByState(boolean state) {
        String sql = "SELECT * FROM association WHERE state = ?";
        Object[] params = {state ? 1 : 0};
        return dbHelper.executeQuery(sql, params, Association.class);
    }

    public List<Association> findAll() {
        String sql = "SELECT * FROM association";
        return dbHelper.executeQuery(sql, new Object[]{}, Association.class);
    }

    public Map<String, Object> findAllWithPagination(int pageNum, int pageSize) {
        int offset = (pageNum - 1) * pageSize;
        String sql = "SELECT * FROM association LIMIT ? OFFSET ?";
        List<Association> associations = dbHelper.executeQuery(sql, new Object[]{pageSize, offset}, Association.class);

        String countSql = "SELECT COUNT(*) FROM association";
        int totalRecords = dbHelper.executeQuery(countSql, new Object[]{}, Integer.class)
                .stream().findFirst().orElse(0);

        int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

        Map<String, Object> result = new HashMap<>();
        result.put("associationList", associations);
        result.put("totalRecords", totalRecords);
        result.put("totalPages", totalPages);
        result.put("currentPage", pageNum);

        return result;
    }

    public void clearData() {
        dbHelper.clearData("association");
    }

    public void dropTable() {
        dbHelper.dropTable("association");
    }
}